﻿
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public void MapICC_PVKPOS(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveICC_PVKPOS(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            Result = QueryICC_PVKPOS(dwData);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }

            MapICC_PVKPOS(ref dwLocal, dwData);

            if (!flgExist)
            {
                dwLocal["ROWNID"] = GetSeq();
                InsertICC_PVKPOS(dwLocal, 1);
            }
            UpdateICC_PVKPOS(dwLocal);
        }
        public void InsertICC_PVKPOS(DataRow dwData, int option = 1)
        {

            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "INSERT INTO ICC_PVKPOS (VKPID,VKPMANDANT,VKPAUFKRS,VKPAUFLFD,VKPAUFPOS,VKPART,VKPFARBE,VAFPART,VAFPVAR,VAFFFARBE,VAFBESCHICH,VKPNEUDAT,VKPMENGE1,VKPQTY,VKPLIEFTERM,VKPERL)"
                    + " SELECT SEQ_ICC_ITX.NEXTVAL AS VKPID, VKPMANDANT,VKPAUFKRS,VKPAUFLFD,VKPAUFPOS,VKPART,VKPFARBE,VAFPART,VAFPVAR,VAFFFARBE,  VAFBESCHICH,VKPNEUDAT,VKPMENGE1,VKPMENGE1*VKPMEEHFKT*0.001*0.001*0.01 AS VKPQTY,VKPLIEFTERM,VKPERL  FROM VKPOS P JOIN VAFARBEN V ON VKPART=VAFART AND VKPFARBE=VAFFARBE AND VAFPART<>'                    ' "
                    +" WHERE VKPMANDANT=:VKPMANDANT AND VKPSA='  ' AND VKPAUFKRS NOT LIKE 'H%'   AND NOT EXISTS (SELECT 1 FROM ICC_PVKPOS X WHERE P.VKPSA='  '  AND P.VKPMANDANT=X.VKPMANDANT AND P.VKPAUFKRS=X.VKPAUFKRS AND P.VKPAUFLFD=X.VKPAUFLFD AND P.VKPAUFPOS=X.VKPAUFPOS )";
                    or1.Parameters.Add("VKPMANDANT", OracleType.NVarChar).Value = dwData["VKPMANDANT"].ToString();
                    break;
                case 2:
                    or1.CommandText = "INSERT INTO ICC_PVKPOS (VKPID,VKPMANDANT,VKPAUFKRS,VKPAUFLFD,VKPAUFPOS,VKPART,VKPFARBE,VAFPART,VAFPVAR,VAFFFARBE,  VAFBESCHICH,VKPNEUDAT,VKPMENGE1,VKPQTY,VKPLIEFTERM,VKPERL)   SELECT SEQ_ICC_ITX.NEXTVAL ,  VKPMANDANT,VKPAUFKRS,VKPAUFLFD,VKPAUFPOS,VKPART,VKPFARBE,VAFPART,VAFPVAR,VAFFFARBE,  VAFBESCHICH,VKPNEUDAT,VKPMENGE1,VKPMENGE1*VKPMEEHFKT*0.001*0.001*0.01 AS VKPQTY,VKPLIEFTERM,VKPERL FROM VKPOS P JOIN VAFARBEN V ON VKPART=VAFART AND VKPFARBE=VAFFARBE AND VAFPART<>'                    ' "
                    + " WHERE VKPMANDANT=:VKPMANDANT AND VKPSA='  ' AND NOT EXISTS (SELECT 1 FROM ICC_PVKPOS X WHERE P.VKPSA='  '  AND P.VKPMANDANT=X.VKPMANDANT AND P.VKPAUFKRS=X.VKPAUFKRS AND P.VKPAUFLFD=X.VKPAUFLFD AND P.VKPAUFPOS=X.VKPAUFPOS )";
                    or1.Parameters.Add("VKPMANDANT", OracleType.NVarChar).Value = dwData["VKPMANDANT"].ToString();
                    break;
                default:
                    break;
            }

            Connect2Proxy(or1);
        }
        public DataSet QueryICC_PVKPOS(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "SELECT * FROM ICC_PVKPOS"
                        + " WHERE VKPMANDANT=:VKPMANDANT AND VKPEND=0";
                    or1.Parameters.Add("VKPMANDANT", OracleType.NVarChar).Value = dwData["VKPMANDANT"].ToString();
                    break;
                case 2:
                    or1.CommandText = "SELECT VKPMANDANT,VKPAUFKRS,VKPAUFLFD,VKPAUFPOS,VKPART,VKPFARBE,VKPMENGE1,VKPZUGET,VKPLIEFTERM,VKPNEUDAT,VKPNEUBEN,VKPRESERV,VKPERL  FROM ICC_PVKPOS M WHERE VKPERL=' ' "
                    + " ORDER BY  VKPNEUDAT,VKPAUFKRS,VKPAUFLFD,VKPAUFPOS";
                    break;
                case 3:
                    or1.CommandText = "SELECT VKPMANDANT,VKPAUFKRS,VKPAUFLFD,VKPAUFPOS,VKPART,VKPFARBE,VKPMENGE1,VKPZUGET,VKPLIEFTERM,VKPNEUDAT,VKPNEUBEN,VKPRESERV,VKPERL  FROM ICC_PVKPOS M"
                    + " WHERE VKPMANDANT=:VKPMANDANT AND VKPAUFKRS LIKE '%"+dwData["VKPAUFKRS"].ToString().Trim()+"%' AND VKPAUFLFD LIKE '%"+dwData["VKPAUFLFD"].ToString().Trim()+"%'"
                    + " ORDER BY  VKPNEUDAT,VKPAUFKRS,VKPAUFLFD,VKPAUFPOS";
                    or1.Parameters.Add("VKPMANDANT", OracleType.NVarChar).Value = dwData["VKPMANDANT"].ToString(); 
                    break;
                case 10:
                    or1.CommandText = "SELECT VKPMANDANT,VKPAUFKRS,VKPAUFLFD,VKPAUFPOS,VKPART,VKPFARBE,VKPMENGE1,VKPZUGET,VKPLIEFTERM,VKPNEUDAT,VKPNEUBEN,VKPRESERV,VKPERL  FROM ICC_PVKPOS M WHERE VKPERL=' ' AND VKPZUGET<VKPMENGE1 AND VKPRESERV=0 "
                    + " ORDER BY  VKPNEUDAT,VKPAUFKRS,VKPAUFLFD,VKPAUFPOS";
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "ICC_PVKPOS";
            Return = Result;
            return Return;
        }
        public void UpdateICC_PVKPOS(DataRow dwData, int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    break;
                case 2:
                    or1.CommandText = "UPDATE ICC_PVKPOS X SET VKKLINES=(SELECT COUNT(*) FROM VKPOS P WHERE VKKLINES=0   AND  VKPMANDANT=:VKPMANDANT AND P.VKPSA='  ' AND P.VKPAUFKRS=X.VKPAUFKRS AND P.VKPAUFLFD=X.VKPAUFLFD )  WHERE VKPEND=0 AND EXISTS  (SELECT 1 FROM VKPOS P    WHERE VKPMANDANT=:VKPMANDANT AND P.VKPSA='  ' AND P.VKPAUFKRS=X.VKPAUFKRS AND P.VKPAUFLFD=X.VKPAUFLFD )";
                    or1.Parameters.Add("VKPMANDANT", OracleType.NVarChar).Value = dwData["VKPMANDANT"].ToString();
                    break;
                case 3:
                    or1.CommandText = "UPDATE ICC_PVKPOS X SET JNUM=(SELECT COUNT(*) FROM WASTKE W JOIN BASTGA B ON B.BAGART=WAKKART AND BAGVAR=WAKKVAR AND  BAGFARBE=WAKKFARBE WHERE VAFPART=WAKART AND VAFPVAR=WAKVAR AND X.VAFFFARBE=WAKWCOL   ) WHERE  JNUM=0 AND VKPEND=0 AND EXISTS  (SELECT 1 FROM WASTKE W JOIN BASTGA B ON B.BAGART=WAKKART AND BAGVAR=WAKKVAR AND BAGFARBE=WAKKFARBE WHERE VAFPART=WAKART AND VAFPVAR=WAKVAR AND X.VAFFFARBE=WAKWCOL)";
                    break;
                case 4:
                    or1.CommandText = "UPDATE ICC_PVKPOS X SET WNUM=(SELECT COUNT(*) FROM  WASTGA B  WHERE VAFPART=WAGART AND WAGVAR=VAFPVAR AND WAGWCOL=VAFFFARBE)   WHERE  WNUM=0 AND VKPEND=0 AND EXISTS (SELECT 1 FROM  WASTGA B WHERE VAFPART=WAGART AND WAGVAR=VAFPVAR AND WAGWCOL=VAFFFARBE)";
                    break;
                case 5:
                    or1.CommandText = "UPDATE ICC_PVKPOS X SET JNUM=(SELECT COUNT(*) FROM GASTGA B WHERE VAFPART=GAGART AND VAFPVAR=SUBSTR(GAGVAR,1,2) AND X.VAFFFARBE=GAGFARBE   ) WHERE  JNUM=0 AND VKPEND=0 AND EXISTS  (SELECT 1 FROM   GASTGA B  WHERE VAFPART=GAGART AND  VAFPVAR=SUBSTR(GAGVAR,1,2) AND X.VAFFFARBE=GAGFARBE)";
                    break;
                case 11:
                    or1.CommandText = "UPDATE ICC_PVKPOS P SET (VKPNEUBEN,VKPFREIGE,VKPOFFEN,VKPWUTERM,VKPERL,VKPERLDAT,VKPZUGET,VKPRESERV,VKPZUSATZ,VKPKDKOMM) =(SELECT VKPNEUBEN,VKPFREIGE,VKPOFFEN,VKPWUTERM,VKPERL,VKPERLDAT,VKPZUGET,VKPRESERV,VKPZUSATZ,VKPKDKOMM FROM VKPOS M WHERE M.VKPSA='  ' AND M.VKPAUFKRS=P.VKPAUFKRS AND M.VKPAUFLFD=P.VKPAUFLFD AND M.VKPAUFPOS=P.VKPAUFPOS) WHERE  P.VKPERL=' ' AND EXISTS (SELECT 1 FROM VKPOS M WHERE M.VKPSA='  ' AND M.VKPAUFKRS=P.VKPAUFKRS AND M.VKPAUFLFD=P.VKPAUFLFD AND M.VKPAUFPOS=P.VKPAUFPOS)";
                    break;
                case 12:
                    //or1.CommandText = "   UPDATE ICC_PVKPOS X SET WDNUM=(SELECT COUNT(*) FROM VAFARBEN V JOIN WASTGA B ON B.WAGART=VAFPART AND WAGVAR=VAFPVAR AND WAGWCOL=VAFFARBE    JOIN ICC_MKRF ON ABPART=WAGGART AND ABPVAR=WAGGVAR AND ABPFARBE=WAGGFARBE AND MKFPLDATV>0   WHERE RTRIM(X.VKPART)=RTRIM(VAFPART) AND X.VKPFARBE=VAFFARBE)    WHERE EXISTS (SELECT 1 FROM VAFARBEN V JOIN WASTGA B ON B.WAGART=VAFPART AND WAGVAR=VAFPVAR AND WAGWCOL=VAFFARBE    JOIN ICC_MKRF ON ABPART=WAGGART AND ABPVAR=WAGGVAR AND ABPFARBE=WAGGFARBE AND MKFPLDATV>0   WHERE RTRIM(X.VKPART)=RTRIM(VAFPART) AND X.VKPFARBE=VAFFARBE )";
                    break;
                case 21:
                    or1.CommandText = "UPDATE ICC_PVKPOS SET JWNUM=JNUM+WNUM,JWDNUM=JDNUM+WDNUM WHERE JNUM>0 AND VKPEND=0 ";
                    break;
                case 22:
                    or1.CommandText = "UPDATE ICC_PVKPOS SET  WDRATIO=ROUND(1000*WDNUM/WNUM) WHERE WNUM>0";
                    break;
                case 23:
                    or1.CommandText = "UPDATE ICC_PVKPOS SET JWDRATIO=ROUND(1000*JWDNUM/JWNUM) WHERE JWNUM>0 ";
                    break;
            }
            Connect2Proxy(or1);
        }
        public DataSet DeleteICC_PVKPOS(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM ICC_PVKPOS WHERE  ABAID=:ABAID"; ;
                    or1.Parameters.Add("ABAID", OracleType.NVarChar).Value = dwData["ABAID"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
            return Result;
        }
    }
}
